package com.trip.service;

import com.trip.admin.vo.PlaceAndExperienceAdminListItemVo;
import com.trip.admin.vo.PlaceAndExperienceDetailAdminVo;
import com.trip.admin.vo.SpecialPlaceAndExperienceDetailVo;
import com.trip.dao.Tables;
import com.trip.dao.tables.User;
import com.trip.dao.tables.pojos.*;
import com.trip.dao.tables.records.*;
import com.trip.vo.*;
import org.apache.commons.lang3.StringUtils;
import org.jooq.Condition;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.SelectConditionStep;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

import static com.trip.dao.Tables.*;
import static com.trip.dao.tables.UserRelated.USER_RELATED;
import static org.jooq.impl.DSL.rand;
import static org.jooq.impl.DSL.round;
import static org.jooq.impl.DSL.trueCondition;

/**
 * Created by hc24 on 2017/9/29.
 */
@Service
@Transactional
public class PlaceAndExperienceService {

    @Autowired
    private DSLContext dslContext;

    public void signIn(Integer userId,String paeId){
        UserRelatedRecord urr= queryUserRelated(userId,paeId);
        int n=0;
        if(urr==null){
            urr=dslContext.newRecord(Tables.USER_RELATED);
            urr.setUserid(userId);
            urr.setPaeid(paeId);
            urr.setVisited(true);
            urr.setVisitedTime(LocalDateTime.now());

            n=urr.insert();
        }else if(urr.getVisited()==null||!urr.getVisited()){
            urr.setVisited(true);
            urr.setVisitedTime(LocalDateTime.now());
            n=urr.update();
        }

        //calc total visited number
        dslContext.update(PLACE_AND_EXP)
                .set(PLACE_AND_EXP.ARRIVED_NUMBER,PLACE_AND_EXP.ARRIVED_NUMBER.plus(n))
                .where(PLACE_AND_EXP.ID.eq(paeId))
                .execute();
    }

    public void cancelSignIn(Integer userId,String paeId){
        UserRelatedRecord urr= queryUserRelated(userId,paeId);
        if(urr!=null){
            urr.setVisited(false);
            urr.setVisitedTime(null);

            urr.update();
        }
        //int count= dslContext.deleteFrom(USER_RELATED).where(USER_RELATED.USERID.eq(userId),USER_RELATED.PAEID.eq(paeId)).execute();
        //calc total visited number
        dslContext.update(PLACE_AND_EXP)
                .set(PLACE_AND_EXP.ARRIVED_NUMBER,PLACE_AND_EXP.ARRIVED_NUMBER.minus(urr==null?0:1))
                .where(PLACE_AND_EXP.ID.eq(paeId))
                .execute();
    }

    public void createNote(Integer userId,String paeId,String note){
        UserRelatedRecord urr= queryUserRelated(userId,paeId);
        if(urr==null){
            urr=dslContext.newRecord(Tables.USER_RELATED);
            urr.setUserid(userId);
            urr.setPaeid(paeId);
            urr.setNote(note);
            urr.setNoteTime(LocalDateTime.now());

            urr.insert();
        }else{
            if(StringUtils.isBlank(note)){
                urr.setNoteTime(null);
                urr.setNote(null);
            }else {
                urr.setNote(note);
                urr.setNoteTime(LocalDateTime.now());
            }

            urr.update();
        }
    }

    public void createReview(Integer userId,String paeId,Integer star,String content){
        UserRelatedRecord urr= queryUserRelated(userId,paeId);
        if(urr==null){
            urr=dslContext.newRecord(Tables.USER_RELATED);
            urr.setUserid(userId);
            urr.setPaeid(paeId);
            urr.setStar(star);
            urr.setReviewContent(content);
            urr.setReviewTime(LocalDateTime.now());

            urr.insert();
        }else{
            urr.setStar(star);
            urr.setReviewContent(content);
            urr.setReviewTime(LocalDateTime.now());
            urr.update();
        }

        /* calc average score */
        PlaceAndExpRecord pae=query(paeId);
        pae.setStarCount((pae.getStarCount()==null?0:pae.getStarCount())+1);
        pae.setStarSum((pae.getStarSum()==null?0:pae.getStarSum())+star);
        pae.setStar(pae.getStarSum()/pae.getStarCount());

        dslContext.update(PLACE_AND_EXP)
                .set(PLACE_AND_EXP.STAR_COUNT,pae.getStarCount())
                .set(PLACE_AND_EXP.STAR_SUM,pae.getStarSum())
                .set(PLACE_AND_EXP.STAR,pae.getStar())
                .where(PLACE_AND_EXP.ID.eq(paeId))
                .execute();
    }


    @Transactional(readOnly = true)
    public Page<PlaceAndExperienceListItemVo> queryList(PlaceAndExperienceQo qo,int start,int pageSize){

        Condition condition=trueCondition();


        if(StringUtils.isNotBlank(qo.getKey())){
 //           scs=scs.and("match(title) against(?)",qo.getKey());
            condition=condition.and("title like ?","%"+qo.getKey()+"%");
        }

        if(qo.getPaeIds()!=null&&qo.getPaeIds().length>0){
//            scs=scs.and(PLACE_AND_EXP.ID.in(qo.getPaeIds()));
            condition=condition.and(PLACE_AND_EXP.ID.in(qo.getPaeIds()));
        }

        if(qo.getType()!=null){
//            scs=scs.and(PLACE_AND_EXP.TYPE.eq(qo.getType()));
            condition=condition.and(PLACE_AND_EXP.TYPE.eq(qo.getType()));
        }
        if(qo.getStates()!=null&&qo.getStates().length>0){
//            scs=scs.and(PLACE_AND_EXP.STATE.in(qo.getStates()));
            condition=condition.and(PLACE_AND_EXP.STATE.in(qo.getStates()));
        }
        if(qo.getTags()!=null&&qo.getTags().length>0){
//            scs=scs.and(PLACE_AND_EXP.ID.in(
//                    dslContext.selectDistinct(PAE_TAGS.PAE_ID).from(PAE_TAGS).where(PAE_TAGS.TAG_CODE.in(qo.getTags()))
//            ));
            condition=condition.and(PLACE_AND_EXP.ID.in(
                    dslContext.selectDistinct(PAE_TAGS.PAE_ID).from(PAE_TAGS).where(PAE_TAGS.TAG_CODE.in(qo.getTags()))
            ));
        }

        SelectConditionStep<Record> scs= dslContext.select(PLACE_AND_EXP.fields()).select(USER_COLLECTION.ID.nvl2(1,0).as("collected"))
                .from(PLACE_AND_EXP)
                .leftJoin(USER_COLLECTION).on(PLACE_AND_EXP.ID.eq(USER_COLLECTION.PAE_ID),USER_COLLECTION.USER_ID.eq(qo.getUserId()))
                .where(condition);

        if(StringUtils.isBlank(qo.getKey())){
            scs.orderBy(rand());
        }
        scs.offset(start).limit(pageSize);

        List<PlaceAndExperienceListItemVo> list=scs.fetchStream().map(e->e.into(PlaceAndExperienceListItemVo.class)).collect(Collectors.toList());

        int total=dslContext.selectCount().from(PLACE_AND_EXP)
                .leftJoin(USER_COLLECTION).on(PLACE_AND_EXP.ID.eq(USER_COLLECTION.PAE_ID),USER_COLLECTION.USER_ID.eq(qo.getUserId()))
                .where(condition).fetchOne().value1();

        return new Page<>(list,start,pageSize,total);
    }

    @Transactional(readOnly = true)
    public Page<PlaceAndExperienceAdminListItemVo> queryAdminList(PlaceAndExperienceQo qo,int start,int pageSize){

        Condition condition=trueCondition();

        if(StringUtils.isNotBlank(qo.getKey())){
            condition=condition.and("title like ?","%"+qo.getKey()+"%");
        }

        if(qo.getType()!=null){
            condition=condition.and(PLACE_AND_EXP.TYPE.eq(qo.getType()));
        }
        if(qo.getStates()!=null&&qo.getStates().length>0){
            condition=condition.and(PLACE_AND_EXP.STATE.in(qo.getStates()));
        }
        if(qo.getTags()!=null&&qo.getTags().length>0){
            condition=condition.and(PLACE_AND_EXP.ID.in(
                    dslContext.selectDistinct(PAE_TAGS.PAE_ID).from(PAE_TAGS).where(PAE_TAGS.TAG_CODE.in(qo.getTags()))
            ));
        }

        SelectConditionStep<Record> scs= dslContext.select(PLACE_AND_EXP.fields()).select(STATE.STATE_NAME)
                .from(PLACE_AND_EXP)
                .leftJoin(STATE).on(PLACE_AND_EXP.STATE.eq(STATE.CODE))
                .where(condition);

        scs.offset(start).limit(pageSize);

        List<PlaceAndExperienceAdminListItemVo> list=scs.fetchStream().map(e->e.into(PlaceAndExperienceAdminListItemVo.class)).collect(Collectors.toList());

        buildTagList(list);

        int total=dslContext.selectCount().from(PLACE_AND_EXP)
                .where(condition).fetchOne().value1();

        return new Page<>(list,start,pageSize,total);
    }

    @Transactional(readOnly = true)
    public List<PlaceAndExperienceAdminListItemVo> queryRelatedAdminList(String id){
        PlaceAndExpRecord record=query(id);

        List<PlaceAndExperienceAdminListItemVo> list=dslContext.select(PLACE_AND_EXP.fields()).select(STATE.STATE_NAME)
                .from(PLACE_AND_EXP)
                .leftJoin(STATE).on(PLACE_AND_EXP.STATE.eq(STATE.CODE))
                .where(PLACE_AND_EXP.ID.in(getRelatedSet(record.getRelated())))
                .fetchInto(PlaceAndExperienceAdminListItemVo.class);
        buildTagList(list);

        return list;
    }

    public void addRelated(String id,String relatedId){
        PlaceAndExpRecord record=query(id);
        Set<String> set=getRelatedSet(record.getRelated());
        set.add(relatedId);

        String newRelated=String.join(",",set.stream().map(e->e.toString()).collect(Collectors.toList()));
        dslContext.update(PLACE_AND_EXP).set(PLACE_AND_EXP.RELATED,newRelated).where(PLACE_AND_EXP.ID.eq(id)).execute();
    }

    public void removeRelated(String id,String relatedId){
        PlaceAndExpRecord record=query(id);
        Set<String> set=getRelatedSet(record.getRelated());
        set.remove(relatedId);

        String newRelated=String.join(",",set.stream().map(e->e.toString()).collect(Collectors.toList()));
        dslContext.update(PLACE_AND_EXP).set(PLACE_AND_EXP.RELATED,newRelated).where(PLACE_AND_EXP.ID.eq(id)).execute();
    }

    private Set<String> getRelatedSet(String related){
        Set<String> set=new HashSet<>();
        if(StringUtils.isBlank(related))
            return set;
        String[] relatedIds=related.split(",");
        for(String relateId:relatedIds){
            set.add(relateId);
        }

        return set;
    }

    private static double R=6371.137;//earth radius km

    @Transactional(readOnly = true)
    public List<PlaceAndExperienceListItemVo> queryNearby(Double lon,Double lat,Double dis,Integer userId){
        double dlng =  2*Math.asin(Math.sin(dis/(2*R))/Math.cos(lat*Math.PI/180));  //⊿λ
        dlng = dlng*180/Math.PI;//
        double dlat = dis/R;  //⊿φ
        dlat = dlat*180/Math.PI;
        double minlat =lat-dlat;
        double maxlat = lat+dlat;
        double minlng = lon -dlng;
        double maxlng = lon + dlng;

        return dslContext.select(PLACE_AND_EXP.fields()).select(USER_COLLECTION.ID.nvl2(1,0).as("collected"))
                .from(PLACE_AND_EXP)
                .leftJoin(USER_COLLECTION).on(PLACE_AND_EXP.ID.eq(USER_COLLECTION.PAE_ID),USER_COLLECTION.USER_ID.eq(userId))
                .where(PLACE_AND_EXP.LON.ge(minlng))
                .and(PLACE_AND_EXP.LON.le(maxlng))
                .and(PLACE_AND_EXP.LAT.ge(minlat))
                .and(PLACE_AND_EXP.LAT.le(maxlat))
                .limit(100)
                .fetchInto(PlaceAndExperienceListItemVo.class);
    }


    @Transactional(readOnly = true)
    public PlaceAndExperienceDetailVo queryDetail(String paeId,Integer userId){
        PlaceAndExpRecord placeAndExpRecord=query(paeId);
        if(placeAndExpRecord==null)
            return null;
        PlaceAndExperienceDetailVo detailVo= placeAndExpRecord.into(PlaceAndExperienceDetailVo.class);
        UserRelatedRecord userRelatedRecord =null;
        if(userId!=null) {
            userRelatedRecord = queryUserRelated(userId, paeId);
        }

        detailVo.setPicList(queryPicByPaeId(paeId));
        detailVo.setUserRelated(userRelatedRecord==null?null:userRelatedRecord.into(UserRelatedDomain.class));
        detailVo.setArrivedUserList(queryArrivedUser(paeId));
        detailVo.setReviewList(queryReviewList(paeId));
        detailVo.setDetailList(queryDetailList(paeId));
        if(userId!=null)
            detailVo.setCollected(isCollected(userId,paeId));
        else
            detailVo.setCollected(null);

        return detailVo;

    }
    @Transactional(readOnly = true)
    public PlaceAndExperienceDetailAdminVo querAdminyDetail(String paeId){
        PlaceAndExpRecord placeAndExpRecord=query(paeId);
        if(placeAndExpRecord==null)
            return null;
        PlaceAndExperienceDetailAdminVo detailVo= placeAndExpRecord.into(PlaceAndExperienceDetailAdminVo.class);


        detailVo.setPicList(queryPicByPaeId(paeId));
        detailVo.setDetailList(queryDetailList(paeId));

        SpecialPlaceAndExperienceDetailVo specialDetail=new SpecialPlaceAndExperienceDetailVo();
        for(PlaceAndExpDetailDomain detailDomain:detailVo.getDetailList()){
            if(detailDomain.getType()==null)
                continue;
            if(detailDomain.getType()==1){
                specialDetail.setHours(detailDomain.getVal());
            }else if(detailDomain.getType()==2){
                specialDetail.setGroupSize(detailDomain.getVal());
            }else if(detailDomain.getType()==3){
                specialDetail.setLanguage(detailDomain.getVal());
            }else if(detailDomain.getType()==4){
                specialDetail.setTransport(detailDomain.getVal());
            }
        }

        detailVo.setSpecialDetail(specialDetail);

        return detailVo;

    }


    private Boolean isCollected(Integer userId,String paeId){
        return dslContext.selectCount().from(USER_COLLECTION)
                .where(USER_COLLECTION.USER_ID.eq(userId),USER_COLLECTION.PAE_ID.eq(paeId))
                .fetchOne().value1()==0?false:true;
    }

    private List<PlaceAndExpDetailDomain> queryDetailList(String paeId){
        return dslContext.selectFrom(PLACE_AND_EXP_DETAIL)
                .where(PLACE_AND_EXP_DETAIL.PAEID.eq(paeId))
                .orderBy(PLACE_AND_EXP_DETAIL.SEQ.asc())
                .fetchInto(PlaceAndExpDetailDomain.class);
    }

    private List<ReviewVo> queryReviewList(String paeId){
       return dslContext.select(User.USER.NICK_NAME,User.USER.ID,User.USER.AVATAR,User.USER.INTRO,USER_RELATED.REVIEW_CONTENT,USER_RELATED.REVIEW_TIME,USER_RELATED.STAR)
                .from(USER_RELATED)
                .leftJoin(USER).on(USER_RELATED.USERID.eq(USER.ID))
                .where(USER_RELATED.PAEID.eq(paeId))
                .and(USER_RELATED.REVIEW_TIME.isNotNull())
                .orderBy(USER_RELATED.REVIEW_TIME.desc())
                .fetchStream().map(e->e.into(ReviewVo.class)).collect(Collectors.toList());
    }

    private List<UserSimpleVo> queryArrivedUser(String paeId){
        return dslContext.select(User.USER.NICK_NAME,User.USER.ID.as("user_id"),User.USER.AVATAR,User.USER.INTRO)
                .from(USER_RELATED)
                .leftJoin(USER).on(USER_RELATED.USERID.eq(USER.ID))
                .where(USER_RELATED.PAEID.eq(paeId))
                .and(USER_RELATED.VISITED.isTrue())
                .orderBy(USER_RELATED.VISITED_TIME.desc())
                .fetchInto(UserSimpleVo.class);
    }

    private List<PicDomain> queryPicByPaeId(String paeId){
        return dslContext.selectFrom(PIC)
                .where(PIC.RELATED_ID.eq(paeId))
                .and(PIC.TYPE.eq(1))
                .orderBy(PIC.SEQ.asc())
                .fetchStream().map(e->e.into(PicDomain.class)).collect(Collectors.toList());
    }

    private void buildTagList(List<PlaceAndExperienceAdminListItemVo> list){
        Map<String,TagRecord> tagMap=new HashMap<>();
        dslContext.selectFrom(TAG).fetch().forEach(e->tagMap.put(e.getTagCode(),e));
        list.forEach(e->{
            if(e.getTags()!=null){
                String[] strTags=e.getTags().split(",");
                for(String tagCode:strTags){
                    if("".equals(tagCode))
                        continue;
                    e.addTagName(tagMap.get(tagCode).getTagName());
                }
            }
        });
    }

    public PlaceAndExpRecord query(String paeId){
        return dslContext.selectFrom(PLACE_AND_EXP)
                .where(PLACE_AND_EXP.ID.eq(paeId))
                .fetchOne();


    }

    public List<PlaceAndExpRecord> query(String[] paeIds){
        return dslContext.selectFrom(PLACE_AND_EXP)
                .where(PLACE_AND_EXP.ID.in(paeIds))
                .fetch();


    }



    private UserRelatedRecord queryUserRelated(Integer userId,String paeId){
        return dslContext.selectFrom(Tables.USER_RELATED)
                .where(USER_RELATED.USERID.eq(userId))
                .and(USER_RELATED.PAEID.eq(paeId))
                .limit(1)
                .fetchOne();
    }

    public void delete(String id){
        dslContext.deleteFrom(PLACE_AND_EXP).where(PLACE_AND_EXP.ID.eq(id)).execute();
        dslContext.deleteFrom(PLACE_AND_EXP_DETAIL).where(PLACE_AND_EXP_DETAIL.PAEID.eq(id)).execute();
        dslContext.deleteFrom(PIC).where(PIC.TYPE.eq(1),PIC.RELATED_ID.eq(id)).execute();
        dslContext.deleteFrom(PAE_TAGS).where(PAE_TAGS.PAE_ID.eq(id)).execute();
        dslContext.deleteFrom(USER_COLLECTION).where(USER_COLLECTION.PAE_ID.eq(id)).execute();
        dslContext.update(USER_RELATED).set(USER_RELATED.VISITED,false).where(USER_RELATED.PAEID.eq(id)).execute();
    }

    @Transactional
    public void save(PlaceAndExpDomain pae, List<PaeTagsDomain> tagList, List<String> picIds, List<PlaceAndExpDetailDomain> detailList){
        PlaceAndExpRecord placeAndExpRecord= dslContext.newRecord(PLACE_AND_EXP,pae);
        placeAndExpRecord.setCreatedAt(LocalDateTime.now());
        if(pae.getId()==null) {

            placeAndExpRecord.insert();
        }else {
            placeAndExpRecord.update();
        }

        String paeId=placeAndExpRecord.getId();

        dslContext.deleteFrom(PAE_TAGS).where(PAE_TAGS.PAE_ID.eq(paeId)).execute();
        dslContext.deleteFrom(PLACE_AND_EXP_DETAIL).where(PLACE_AND_EXP_DETAIL.PAEID.eq(paeId)).execute();

        List<PaeTagsRecord> tagRecordList=tagList.stream().map(e->{
            e.setPaeId(paeId);
            PaeTagsRecord record=dslContext.newRecord(PAE_TAGS);
            record.from(e);
            return record;
        }).collect(Collectors.toList());

        List<PlaceAndExpDetailRecord> detailRecordList=detailList.stream().map(e->{
            e.setPaeid(paeId);
            PlaceAndExpDetailRecord record=dslContext.newRecord(PLACE_AND_EXP_DETAIL);
            record.from(e);
            return record;
        }).collect(Collectors.toList());

        dslContext.batchInsert(tagRecordList).execute();
        dslContext.batchInsert(detailRecordList).execute();
        dslContext.update(PIC).set(PIC.RELATED_ID,paeId).where(PIC.ID.in(picIds)).execute();

    }

}
